﻿using System;
using System.Diagnostics;
using System.Collections;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Excel;
using System.Text;
using Maticsoft.DBUtility;
namespace Maticsoft.Common
{
    /// <summary>
    /// 操作EXCEL导出签收表的类
    /// Copyright (C) Maticsoft
    /// </summary>
    public class DocDataToExcel
    {
        public DocDataToExcel()
        {
        }

        #region 操作EXCEL的一个类(需要Excel.dll支持)

        private int titleColorindex = 15;
        /// <summary>
        /// 标题背景色
        /// </summary>
        public int TitleColorIndex
        {
            set { titleColorindex = value; }
            get { return titleColorindex; }
        }

        private DateTime beforeTime;			//Excel启动之前时间
        private DateTime afterTime;				//Excel启动之后时间

        #region 创建一个Excel示例
        /// <summary>
        /// 创建一个Excel示例
        /// </summary>
        public void CreateExcel()
        {
            Excel.Application excel = new Excel.Application();
            excel.Application.Workbooks.Add(true);
            excel.Cells[1, 1] = "第1行第1列";
            excel.Cells[1, 2] = "第1行第2列";
            excel.Cells[2, 1] = "第2行第1列";
            excel.Cells[2, 2] = "第2行第2列";
            excel.Cells[3, 1] = "第3行第1列";
            excel.Cells[3, 2] = "第3行第2列";

            //保存
            excel.ActiveWorkbook.SaveAs("./tt.xls", XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
            //打开显示
            excel.Visible = true;
            //			excel.Quit();
            //			excel=null;            
            //			GC.Collect();//垃圾回收
        }
        #endregion

        #region 将DataTable的数据导出显示为报表
        /// <summary>
        /// 将DataTable的数据导出显示为签收表
        /// </summary>
        /// <param name="dt">要导出的数据</param>
        ///  <param name="Name">单证模板名</param>
        /// <param name="strTitle">导出报表的标题</param>
        /// <param name="FilePath">保存文件的路径</param>
        /// <returns></returns>
        public string OutputExcel(System.Data.DataTable dt,string user, string strTitle, string FilePath)
        {
            beforeTime = DateTime.Now;

            Excel.Application excel;
            Excel._Workbook xBk;
            Excel._Worksheet xSt;

            int rowIndex = 3;
            int colIndex = 0;
            int sum=0;
            
            System.Data.DataTable dt2 = Getdoc(user);
            excel = new Excel.ApplicationClass();
            xBk = excel.Workbooks.Add(true);
            xSt = (Excel._Worksheet)xBk.ActiveSheet;
		//委托单信息
            excel.Cells[rowIndex, 1] = "客户:";
            excel.Cells[rowIndex, 2] = dt.Rows[0][1].ToString();
            excel.Cells[rowIndex, dt2.Rows.Count + 8] = "编号:";
            excel.Cells[rowIndex, dt2.Rows.Count + 9] = dt.Rows[0][2].ToString();
            rowIndex += 2;
            excel.Cells[rowIndex, 1] = "序号";
            excel.Cells[rowIndex, 2] = "日期";
            excel.Cells[rowIndex, 3] = "船名";
            excel.Cells[rowIndex, 4] = "货名";
            excel.Cells[rowIndex, 5] = "提单数";
            excel.Cells[rowIndex, 6] = "提货单号";
            excel.Cells[rowIndex, 7] = "报关单号";
            excel.Cells[rowIndex, dt2.Rows.Count + 10] = "份数";
            rowIndex++;
            
            if (dt != null)
            {
                //序号
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    excel.Cells[rowIndex, 1] = i + 1;
                    for (int j = 0; j < dt2.Rows.Count; j++)
                    {
                        excel.Cells[rowIndex, j + 8] = "1";
                    }
                    excel.Cells[rowIndex, dt2.Rows.Count + 10] = dt2.Rows.Count;
                    rowIndex++;
                }
                rowIndex -= (dt.Rows.Count+1) ;
                //读取数据
                for (int x = 0; x < dt.Rows.Count; x++)
                {
                    rowIndex++;
                    for (int y = 0; y < dt.Columns.Count-4; y++)
                    {
                        excel.Cells[rowIndex, y +2] = dt.Rows[x][y+3].ToString();
                    }
                }
                rowIndex -=(dt.Rows.Count);

                        
                //单证信息
                for (int x = 0; x < dt2.Rows.Count; x++)
                {
                    excel.Cells[rowIndex, 8 + x] = dt2.Rows[x][0].ToString();
                }
                
            }
            //单元格格式，左对齐
            xSt.get_Range(excel.Cells[3, 1], excel.Cells[rowIndex, 4]).Select();
            xSt.get_Range(excel.Cells[3, 1], excel.Cells[rowIndex, 4]).HorizontalAlignment = XlHAlign.xlHAlignLeft;
            xSt.get_Range(excel.Cells[3, 4], excel.Cells[rowIndex, 4]).WrapText = true;//自动换行
            
            xSt.get_Range(excel.Cells[6, 8], excel.Cells[1024,30]).Select();
            xSt.get_Range(excel.Cells[6, 8], excel.Cells[1024,30]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

            xSt.get_Range(excel.Cells[6, 1], excel.Cells[1024, 1]).Select();
            xSt.get_Range(excel.Cells[6, 1], excel.Cells[1024, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

            xSt.get_Range(excel.Cells[6, 2], excel.Cells[1024, 2]).NumberFormat = "yyyy-mm-dd";

          
            //加载一个合计行			
            int rowSum = rowIndex + dt.Rows.Count+3;
            int colSum = 1;
            excel.Cells[rowSum, dt2.Rows.Count + 9] = "合计";
            excel.Cells[rowSum, dt2.Rows.Count + 10] = dt.Rows.Count * dt2.Rows.Count;
            xSt.get_Range(excel.Cells[rowSum, dt2.Rows.Count + 9], excel.Cells[rowSum, dt2.Rows.Count + 9]).Font.Size = 14;
            xSt.get_Range(excel.Cells[rowSum, dt2.Rows.Count + 9], excel.Cells[rowSum, dt2.Rows.Count + 9]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            //设置选中的部分的颜色			
            //xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
            //xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色，共计有56种
           
            
            
            //签收内容
            rowSum = rowSum + 1;
            xSt.get_Range(excel.Cells[rowSum + 2, 2], excel.Cells[rowSum + 2, 3]).Merge();//合并单元格
            excel.Cells[rowSum + 2, 2] = "收到以上单证核对后";
            xSt.get_Range(excel.Cells[rowSum+2, 4], excel.Cells[rowSum+2, 6]).Merge();
            excel.Cells[rowSum + 2, 4] = "请将签收表回传";
            xSt.get_Range(excel.Cells[rowSum + 2, 7], excel.Cells[rowSum + 2, 9]).Merge();
            excel.Cells[rowSum + 2, 7] = "我司！谢谢！传真：0756-7269213";
            excel.Cells[rowSum + 5, dt2.Rows.Count + 7] = "签收人：";
            excel.Cells[rowSum + 7, dt2.Rows.Count + 7] = "日期：";

            
            //签收内容格式
            xSt.get_Range(excel.Cells[rowSum + 2, 2], excel.Cells[rowSum + 2, 2]).Font.Size = 12;
            xSt.get_Range(excel.Cells[rowSum + 2, 4], excel.Cells[rowSum + 2, 4]).Font.Bold = true;
            xSt.get_Range(excel.Cells[rowSum + 2, 4], excel.Cells[rowSum + 2, 4]).Font.Size = 22;
            xSt.get_Range(excel.Cells[rowSum + 2, 7], excel.Cells[rowSum + 2, 7]).Font.Size = 12;
            xSt.get_Range(excel.Cells[rowSum + 5, 1], excel.Cells[rowSum + 5, 1]).Font.Size = 15;
            xSt.get_Range(excel.Cells[rowSum + 7, 1], excel.Cells[rowSum + 7, 1]).Font.Size = 15;

            //取得整个报表的标题			
            excel.Cells[1, 1] = strTitle;

            //设置整个报表的标题格式			
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, dt2.Rows.Count + 10]).Font.Bold = true;
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, dt2.Rows.Count + 10]).Font.Size = 18;

            ////设置报表表格为最适应宽度			
            xSt.get_Range(excel.Cells[3, 1], excel.Cells[1024, 100]).Select();
            xSt.get_Range(excel.Cells[3, 1], excel.Cells[1024, 100]).Columns.AutoFit();
           


            //设置整个报表的标题为跨列居中			
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, dt2.Rows.Count + 10]).Select();
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, dt2.Rows.Count + 10]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;

            //绘制边框			
            xSt.get_Range(excel.Cells[5, 1], excel.Cells[rowSum, dt2.Rows.Count + 10]).Borders.LineStyle = 1;
            xSt.get_Range(excel.Cells[5, 1], excel.Cells[rowSum, 1]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗
            xSt.get_Range(excel.Cells[5, 1], excel.Cells[5, dt2.Rows.Count + 10]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗
            xSt.get_Range(excel.Cells[5, dt2.Rows.Count + 10], excel.Cells[rowSum, dt2.Rows.Count + 10]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗
            xSt.get_Range(excel.Cells[rowSum, 1], excel.Cells[rowSum, dt2.Rows.Count + 10]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗


            //加备注行
            excel.Cells[rowSum ,1] = "备注";
            xSt.get_Range(excel.Cells[rowSum , 2], excel.Cells[rowSum , dt2.Rows.Count + 10]).Merge();
            xSt.get_Range(excel.Cells[rowSum , 1], excel.Cells[rowSum , dt2.Rows.Count + 10]).Columns.RowHeight = 45;
            
            //单元格格式，左对齐
            xSt.get_Range(excel.Cells[3, 2], excel.Cells[rowIndex+1, 2]).Select();
            xSt.get_Range(excel.Cells[3, 2], excel.Cells[rowIndex+1, 2]).HorizontalAlignment = XlHAlign.xlHAlignLeft;
            xSt.get_Range(excel.Cells[3, 2], excel.Cells[rowIndex+1, 2]).WrapText = true;//自动换行


            afterTime = DateTime.Now;

            //表名效果
            xSt.Name = "签收表";
            

            ClearFile(FilePath);
            string filename = DateTime.Now.ToString("yyyyMMddHHmmss") + "-" + dt.Rows[0]["sheetID"].ToString() + dt.Rows[0]["客户名"].ToString() + ".xls";
            
            
            //判断是否存在
            
            //if(System.IO.File.Exists(filename))
            //{
            //    System.IO.File.Delete(filename);
            //}

            excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel7, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

            //wkbNew.SaveAs strBookName;
            //excel.Save(strExcelFileName);

            #region  结束Excel进程

            //需要对Excel的DCOM对象进行配置:dcomcnfg


            //excel.Quit();
            //excel=null;            

            xBk.Close(null, null, null);
            excel.Workbooks.Close();
            excel.Quit();


            //注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程
            //			if(rng != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
            //				rng = null;
            //			}
            //			if(tb != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
            //				tb = null;
            //			}
            if (xSt != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
                xSt = null;
            }
            if (xBk != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
                xBk = null;
            }
            if (excel != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                excel = null;
            }
            GC.Collect();//垃圾回收
            #endregion

            return filename;

        }
        #endregion

        #region Kill Excel进程

        /// <summary>
        /// 结束Excel进程
        /// </summary>
        public void KillExcelProcess()
        {
            Process[] myProcesses;
            DateTime startTime;
            myProcesses = Process.GetProcessesByName("Excel");

            //得不到Excel进程ID，暂时只能判断进程启动时间
            foreach (Process myProcess in myProcesses)
            {
                startTime = myProcess.StartTime;
                if (startTime > beforeTime && startTime < afterTime)
                {
                    myProcess.Kill();
                }
            }
        }
        #endregion

        #endregion

        #region 将DataTable的数据导出显示为报表(不使用Excel对象，使用COM.Excel)

        #region 使用示例
        /*使用示例：
		 * DataSet ds=(DataSet)Session["AdBrowseHitDayList"];
			string ExcelFolder=Assistant.GetConfigString("ExcelFolder");
			string FilePath=Server.MapPath(".")+"\\"+ExcelFolder+"\\";
			
			//生成列的中文对应表
			Hashtable nameList = new Hashtable();
			nameList.Add("ADID", "广告编码");
			nameList.Add("ADName", "广告名称");
			nameList.Add("year", "年");
			nameList.Add("month", "月");
			nameList.Add("browsum", "显示数");
			nameList.Add("hitsum", "点击数");
			nameList.Add("BrowsinglIP", "独立IP显示");
			nameList.Add("HitsinglIP", "独立IP点击");
			//利用excel对象
			DataToExcel dte=new DataToExcel();
			string filename="";
			try
			{			
				if(ds.Tables[0].Rows.Count>0)
				{
					filename=dte.DataExcel(ds.Tables[0],"标题",FilePath,nameList);
				}
			}
			catch
			{
				//dte.KillExcelProcess();
			}
			
			if(filename!="")
			{
				Response.Redirect(ExcelFolder+"\\"+filename,true);
			}
		 * 
		 * */

        #endregion

        /// <summary>
        /// 将DataTable的数据导出显示为报表(不使用Excel对象)
        /// </summary>
        /// <param name="dt">数据DataTable</param>
        /// <param name="strTitle">标题</param>
        /// <param name="FilePath">生成文件的路径</param>
        /// <param name="nameList"></param>
        /// <returns></returns>
        public string DataExcel(System.Data.DataTable dt,string strTitle, string FilePath, Hashtable nameList)
        {
            COM.Excel.cExcelFile excel = new COM.Excel.cExcelFile();
            ClearFile(FilePath);
            string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
            excel.CreateFile(FilePath + filename);
            excel.PrintGridLines = false;

            COM.Excel.cExcelFile.MarginTypes mt1 = COM.Excel.cExcelFile.MarginTypes.xlsTopMargin;
            COM.Excel.cExcelFile.MarginTypes mt2 = COM.Excel.cExcelFile.MarginTypes.xlsLeftMargin;
            COM.Excel.cExcelFile.MarginTypes mt3 = COM.Excel.cExcelFile.MarginTypes.xlsRightMargin;
            COM.Excel.cExcelFile.MarginTypes mt4 = COM.Excel.cExcelFile.MarginTypes.xlsBottomMargin;

            double height = 1.5;
            excel.SetMargin(ref mt1, ref height);
            excel.SetMargin(ref mt2, ref height);
            excel.SetMargin(ref mt3, ref height);
            excel.SetMargin(ref mt4, ref height);

            COM.Excel.cExcelFile.FontFormatting ff = COM.Excel.cExcelFile.FontFormatting.xlsNoFormat;
            string font = "宋体";
            short fontsize = 9;
            excel.SetFont(ref font, ref fontsize, ref ff);

            byte b1 = 1,
                b2 = 12;
            short s3 = 12;
            excel.SetColumnWidth(ref b1, ref b2, ref s3);

            string header = "页眉";
            string footer = "页脚";
            excel.SetHeader(ref header);
            excel.SetFooter(ref footer);


            COM.Excel.cExcelFile.ValueTypes vt = COM.Excel.cExcelFile.ValueTypes.xlsText;
            COM.Excel.cExcelFile.CellFont cf = COM.Excel.cExcelFile.CellFont.xlsFont0;
            COM.Excel.cExcelFile.CellAlignment ca = COM.Excel.cExcelFile.CellAlignment.xlsCentreAlign;
            COM.Excel.cExcelFile.CellHiddenLocked chl = COM.Excel.cExcelFile.CellHiddenLocked.xlsNormal;

            // 报表标题
            int cellformat = 1;
            //			int rowindex = 1,colindex = 3;					
            //			object title = (object)strTitle;
            //			excel.WriteValue(ref vt, ref cf, ref ca, ref chl,ref rowindex,ref colindex,ref title,ref cellformat);

            int rowIndex = 1;//起始行
            int colIndex = 0;



            //取得列标题				
            foreach (DataColumn colhead in dt.Columns)
            {
                colIndex++;
                string name = colhead.ColumnName.Trim();
                object namestr = (object)name;
                IDictionaryEnumerator Enum = nameList.GetEnumerator();
                while (Enum.MoveNext())
                {
                    if (Enum.Key.ToString().Trim() == name)
                    {
                        namestr = Enum.Value;
                    }
                }
                excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref namestr, ref cellformat);
            }

            //取得表格中的数据			
            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    if (col.DataType == System.Type.GetType("System.DateTime"))
                    {
                        object str = (object)(Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd"); ;
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                    }
                    else
                    {
                        object str = (object)row[col.ColumnName].ToString();
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                    }
                }
            }
            int ret = excel.CloseFile();

            //			if(ret!=0)
            //			{
            //				//MessageBox.Show(this,"Error!");
            //			}
            //			else
            //			{
            //				//MessageBox.Show(this,"请打开文件c:\\test.xls!");
            //			}
            return filename;

        }

        #endregion

        #region  清理过时的Excel文件

        private void ClearFile(string FilePath)
        {
            String[] Files = System.IO.Directory.GetFiles(FilePath);
            if (Files.Length > 1)
            {
                for (int i = 0; i < 1; i++)
                {
                    try
                    {
                        System.IO.File.Delete(Files[i]);
                    }
                    catch
                    {
                    }

                }
            }
        }
        #endregion
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public System.Data.DataTable Getdoc(string  id)
        {
            StringBuilder strsql = new StringBuilder();
            strsql.Append(@"select a.codename,a.num from  info_document as a join info_documentmastert as b on (a.docviewid=b.docviewid) ");
            strsql.Append(" where b.docviewid=" + id);

            return DbHelperSQL.Query(strsql.ToString()).Tables[0];
        }

    }
}
